﻿Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.Odbc
Imports Entidad

Public Class DocumentoClienteBultoDetalleDatos

    Private Path As String = "H:\DATOS\PRUEBA\"

    Function generarCodigoBultoDetalle() As Integer
        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar()
        coneccion.Open()
        Dim query As String = "SELECT COUNT(*) + 1 AS CONTAR FROM Documento_Cliente_Bulto_Detalle"

        Dim adapter As New SqlDataAdapter(query, coneccion)
        Dim ds As New DataSet
        adapter.Fill(ds)

        coneccion.Close()

        Return CInt(ds.Tables(0).Rows(0)("CONTAR").ToString.Trim)
    End Function

    Sub registrarDocumentoBultoDetalle(detalle As DocumentoBultoDetalle)
        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar()
        coneccion.Open()
        Dim query As String = "INSERT INTO Documento_Cliente_Bulto_Detalle(CIA,SUC_Codigo,TDOC_Codigo,DCI_Codigo, " & _
                              "DCB_Codigo,DBD_Codigo,ART_Codigo,DBD_Cantidad,DBD_Activo) " & _
                              "VALUES(@CIA,@SUC,@TDOC,@DCICOD,@DCBCOD,@DBDCOD,@ARTCOD,@DBDCANT,'1')"

        Dim comando As New SqlCommand
        With comando
            .Connection = coneccion
            .CommandText = query
            .Parameters.AddWithValue("@CIA", detalle.CIA)
            .Parameters.AddWithValue("@SUC", detalle.SUC_Codigo)
            .Parameters.AddWithValue("@TDOC", detalle.TDOC_Codigo)
            .Parameters.AddWithValue("@DCICOD", detalle.DCI_Codigo)
            .Parameters.AddWithValue("@DCBCOD", detalle.DCB_Codigo)
            .Parameters.AddWithValue("@DBDCOD", detalle.DBD_Codigo)
            .Parameters.AddWithValue("@ARTCOD", detalle.ART_Codigo)
            .Parameters.AddWithValue("@DBDCANT", detalle.DBD_Cantidad)
            .ExecuteNonQuery()
        End With
        coneccion.Close()
    End Sub

    Function obtenerDetalleBulto(codigo As Integer) As DataSet
        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar()
        coneccion.Open()
        Dim query As String = "SELECT dcbd.DBD_Codigo,a.ART_Descripcion, dcbd.DBD_Cantidad " &
                              "FROM Documento_Cliente_Bulto_Detalle dcbd " &
                              "INNER JOIN Articulo a " &
                              "ON dcbd.ART_Codigo = a.ART_Codigo AND a.ART_Activo = '1' " &
                              "WHERE dcbd.DCB_Codigo = " & codigo & " AND dcbd.DBD_Activo = '1'"

        Dim adapter As New SqlDataAdapter(query, coneccion)
        Dim ds As New DataSet
        adapter.Fill(ds)

        coneccion.Close()

        Return ds
    End Function

    Sub actualizarDocumentoBultoDetalle(detalle As DocumentoBultoDetalle)
        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar()
        coneccion.Open()
        Dim query As String = "UPDATE Documento_Cliente_Bulto_Detalle " &
                              "SET DBD_Cantidad = @cantidad " &
                              "WHERE DCB_Codigo = @dcbCodigo " &
                              "AND DBD_Codigo = @dbdCodigo " &
                              "AND DBD_Activo = '1'"

        Dim comando As New SqlCommand
        With comando
            .Connection = coneccion
            .CommandText = query
            .Parameters.AddWithValue("@cantidad", detalle.DBD_Cantidad)
            .Parameters.AddWithValue("@dcbCodigo", detalle.DCB_Codigo)
            .Parameters.AddWithValue("@dbdCodigo", detalle.DBD_Codigo)
            .ExecuteNonQuery()
        End With
        coneccion.Close()
    End Sub

    Sub eliminarDocumentoBultoDetalle(detalle As DocumentoBultoDetalle)
        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar()
        coneccion.Open()
        Dim query As String = "UPDATE Documento_Cliente_Bulto_Detalle " &
                              "SET DBD_Activo = '0' " &
                              "WHERE DBD_Codigo = @CodigoDetalle " &
                              "AND DCB_Codigo = @CodigoBulto " &
                              "AND DBD_Activo = '1'"

        Dim comando As New SqlCommand
        With comando
            .Connection = coneccion
            .CommandText = query
            .Parameters.AddWithValue("@CodigoDetalle", detalle.DBD_Codigo)
            .Parameters.AddWithValue("@CodigoBulto", detalle.DCB_Codigo)
            .ExecuteNonQuery()
        End With
        coneccion.Close()
    End Sub

    Sub registrarCTRG02(gen As Generico)
        Dim query As String = "INSERT INTO CTRG02(DOCTO,BULTO,DETALLE,ITEM,CANTIDAD,CODSUC,CODUSU,FCHPRO,HORPRO,ESTPRO,PASPRO) " & _
                              "VALUES('" & gen.DOCTO & "'," & gen.ID & "," & gen.DETALLE & ",'" & gen.ITEM & "'," & gen.CANTIDAD & ",'" & gen.CODSUC & "'," & _
                              gen.CODUSU & ",{" & gen.FCHPRO & "},'" & gen.HORPRO & "','" & gen.ESTPRO & "','" & gen.PASPRO & "')"

        Dim sConn As String = "Driver={Microsoft Visual FoxPro Driver};SourceType=DBF;SourceDB=" & System.IO.Path.GetDirectoryName(Me.Path + "CTRG02.DBF") & ";"

        Using dbConn As New System.Data.Odbc.OdbcConnection(sConn)
            Try
                dbConn.Open()
                Dim comando As New OdbcCommand
                With comando
                    .Connection = dbConn
                    .CommandText = query
                    .ExecuteNonQuery()
                End With
            Catch ex As Exception
                MsgBox(ex.Message)
            Finally
                dbConn.Close()
            End Try
        End Using
    End Sub

    Sub actualizarCTRG02(gen As Generico)
        Dim query As String = "UPDATE CTRG02 SET CANTIDAD = " & gen.CANTIDAD & _
                              " WHERE DOCTO = '" & gen.DOCTO & "' AND BULTO = " & gen.ID & " AND DETALLE = " & gen.DETALLE

        Dim sConn As String = "Driver={Microsoft Visual FoxPro Driver};SourceType=DBF;SourceDB=" & System.IO.Path.GetDirectoryName(Me.Path + "CTRG02.DBF") & ";"

        Using dbConn As New System.Data.Odbc.OdbcConnection(sConn)
            Try
                dbConn.Open()
                Dim comando As New OdbcCommand
                With comando
                    .Connection = dbConn
                    .CommandText = query
                    .ExecuteNonQuery()
                End With
            Catch ex As Exception
                MsgBox(ex.Message)
            Finally
                dbConn.Close()
            End Try
        End Using
    End Sub

    Sub eliminarCTRG02(gen As Generico)
        Dim query As String = "DELETE FROM CTRG02 WHERE DOCTO = '" & gen.DOCTO & "' AND BULTO = " & gen.ID

        Dim sConn As String = "Driver={Microsoft Visual FoxPro Driver};SourceType=DBF;SourceDB=" & System.IO.Path.GetDirectoryName(Me.Path + "CTRG02.DBF") & ";"

        Using dbConn As New System.Data.Odbc.OdbcConnection(sConn)
            Try
                dbConn.Open()
                Dim comando As New OdbcCommand
                With comando
                    .Connection = dbConn
                    .CommandText = query
                    .ExecuteNonQuery()
                End With
            Catch ex As Exception
                MsgBox(ex.Message)
            Finally
                dbConn.Close()
            End Try
        End Using
    End Sub

    Sub eliminarCTRG02XDetalle(gen As Generico)
        Dim query As String = "DELETE FROM CTRG02 WHERE DOCTO = '" & gen.DOCTO & "' AND BULTO = " & gen.ID & " AND DETALLE = " & gen.DETALLE

        Dim sConn As String = "Driver={Microsoft Visual FoxPro Driver};SourceType=DBF;SourceDB=" & System.IO.Path.GetDirectoryName(Me.Path + "CTRG02.DBF") & ";"

        Using dbConn As New System.Data.Odbc.OdbcConnection(sConn)
            Try
                dbConn.Open()
                Dim comando As New OdbcCommand
                With comando
                    .Connection = dbConn
                    .CommandText = query
                    .ExecuteNonQuery()
                End With
            Catch ex As Exception
                MsgBox(ex.Message)
            Finally
                dbConn.Close()
            End Try
        End Using
    End Sub

    Function listarDetalleBulto(codBulto As String) As DataSet
        Dim conex As New Conexion
        Dim coneccion As SqlConnection = conex.conectar()
        coneccion.Open()
        Dim query As String = "SELECT dcbd.ART_Codigo,a.ART_Descripcion, " &
                              "dcbd.DBD_Cantidad, um.UNM_Abreviatura " &
                              "FROM Documento_Cliente_Bulto_Detalle dcbd " &
                              "INNER JOIN Articulo a " &
                              "ON a.ART_Codigo = dcbd.ART_Codigo AND a.ART_Activo = '1' " &
                              "INNER JOIN UnidadMedida um " &
                              "ON um.UNM_Codigo = a.UNM_Codigo AND um.UNM_Activo = '1' " &
                              "WHERE dcbd.DCB_Codigo = @BUL AND dcbd.DBD_Activo = '1'"

        Dim comando As New SqlCommand(query, coneccion)
        With comando
            .Parameters.AddWithValue("@BUL", codBulto)
        End With
        Dim adapter As New SqlDataAdapter(comando)
        Dim ds As New DataSet
        adapter.Fill(ds)

        coneccion.Close()

        Return ds
    End Function

End Class
